package dbutils;

import exttable.dbclasses.Probationer;
import exttable.formgenerator.StaticMethods;
import utils.Localisation;

import javax.swing.*;
import javax.swing.border.Border;
import java.awt.*;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by IntelliJ IDEA.
 * User: ADMIN
 * Date: 05.09.11
 * Time: 14:04
 * To change this template use File | Settings | File Templates.
 */
public class DeleteProbationer {

    Object selectedItem;
    static JComboBox jcb;
    JPanel panel;
    JButton deleteButton;

    public static JComboBox getJcb() {
        return jcb;
    }

    public DeleteProbationer() throws Exception {
        JFrame chooseFrame = new JFrame();
        panel = new JPanel();
        Border border = BorderFactory.createLineBorder(Color.BLACK, 2);
        panel.setBorder(border);
        jcb = new
                JComboBox(StaticMethods.getProbationerList().toArray());
        panel.add(jcb);
        deleteButton = new JButton(Localisation.getMessage("labelRemove"));


        panel.add(deleteButton);
        chooseFrame.add(panel);
        chooseFrame.setDefaultCloseOperation(JFrame.DISPOSE_ON_CLOSE);
        chooseFrame.setVisible(true);
        chooseFrame.setSize(500, 200);
        init();
    }

    void init() {

        selectedItem = jcb.getSelectedItem();
        jcb.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                if (e.getSource() != null) {
                    JComboBox jcmbType = (JComboBox) e.getSource();
                    selectedItem = jcmbType.getSelectedItem();
                }
            }
        });
        deleteButton.addActionListener(new ActionListener() {
            @Override
            public void actionPerformed(ActionEvent e) {
                deleteProbationer((Probationer) jcb.getSelectedItem());

            }
        });

    }

    private static void deleteProbationer(Probationer probationer) {
        deleteDataFromTests(getTestNameList(probationer.getGroup_id()), getTestIdList(probationer.getProbationer_id()));
        deleteDataFromResultAnswers(probationer.getProbationer_id());
        deleteDataFromVisitDetails(probationer.getProbationer_id());
        deleteDataFromProbationers(probationer);
    }

    private static List<String> getTestIdList(int probationerId) {
        List<String> testIdList = new ArrayList<String>();
        Connection conn;
        PreparedStatement ps;
        ResultSet rs;
        try {
            conn = DbUtils.getDBConnectionGeneral();
            ps = conn.prepareStatement("SELECT test_id FROM visit_details WHERE probationer_id=?");
            ps.setInt(1, probationerId);
            rs = ps.executeQuery();
            while (rs.next()) {
                testIdList.add(rs.getString("test_id"));
            }
            rs.close();
            ps.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return testIdList;
    }

    private static String getListToString(List<String> stringList) {
        String toReturn = "";
        if (stringList.size() != 0) {
            for (String string : stringList) {
                toReturn = toReturn + "'" + string + "', ";
            }
            toReturn = toReturn.substring(0, toReturn.length() - 2);
        }

        return toReturn;
    }

    private static List<String> getTestNameList(int groupId) {
        List<String> testNameList = new ArrayList<String>();
        Connection conn;
        PreparedStatement ps;
        ResultSet rs;
        try {
            conn = DbUtils.getDBConnectionGeneral();
            ps = conn.prepareStatement("SELECT  " +
                    " DISTINCT " +
                    " tpu.analysis_instr_invest_list_id," +
                    " daiil.analysis_name" +
                    " FROM timetable AS tt" +
                    " INNER JOIN test_parametrs_units AS tpu ON(tt.analysis_version_key=tpu.analysis_version_key)" +
                    " INNER JOIN dic_analysis_instr_invest_list AS daiil ON(tpu.analysis_instr_invest_list_id=daiil.analysis_instr_invest_list_id)\n" +
                    " WHERE groups_schema_visits_id IN(" +
                    " SELECT groups_schema_visits_id FROM groups_test_type" +
                    " WHERE group_id=?" +
                    ")" +
                    "ORDER BY tpu.analysis_instr_invest_list_id;");
            ps.setInt(1, groupId);
            rs = ps.executeQuery();
            while (rs.next()) {
                testNameList.add(rs.getString("analysis_name"));
            }
            rs.close();
            ps.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return testNameList;
    }

    private static void deleteDataFromTests(List<String> testNameList, List<String> testIdList) {
        String condition = getListToString(testIdList);
        List<String> queryList = new ArrayList<String>();
        Connection conn;
        PreparedStatement ps = null;
        for (String tableName : testNameList) {
            queryList.add("DELETE FROM " + tableName + " WHERE test_id IN(" + condition + ");");
        }

        try {
            conn = DbUtils.getDBConnectionGeneral();
            for (String query : queryList) {
                ps = conn.prepareStatement(query);
                ps.executeUpdate();
            }
            ps.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }

    private static void deleteDataFromResultAnswers(int probationerId) {
        Connection conn;
        PreparedStatement ps;
        try {
            conn = DbUtils.getDBConnectionGeneral();
            ps = conn.prepareStatement("DELETE FROM result_answers WHERE probationer_id =?");
            ps.setInt(1, probationerId);
            ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void deleteDataFromVisitDetails(int probationerId) {
        Connection conn;
        PreparedStatement ps;
        try {
            conn = DbUtils.getDBConnectionGeneral();
            ps = conn.prepareStatement("DELETE FROM visit_details WHERE probationer_id =?");
            ps.setInt(1, probationerId);
            ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void deleteFromProbationersGroups(String probationerCode) {
        Connection conn;
        PreparedStatement ps;
        try {
            conn = DbUtils.getDBConnectionGeneral();
            ps = conn.prepareStatement("DELETE FROM probationers_groups WHERE probationer_code =?");
            ps.setString(1, probationerCode);
            ps.executeUpdate();
            ps.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    private static void deleteDataFromProbationers(Probationer probationer) {
        Connection conn;
        PreparedStatement ps;
        try {
            conn = DbUtils.getDBConnectionGeneral();
            ps = conn.prepareStatement("DELETE FROM probationers WHERE probationer_id =?");
            ps.setInt(1, probationer.getProbationer_id());
            if (ps.executeUpdate() < 1) {
                JOptionPane.showMessageDialog(null, Localisation.getMessage("error.applicationerror"), "error",
                        JOptionPane.ERROR_MESSAGE);
            } else {
                deleteFromProbationersGroups(probationer.getProbationer_code());
                JOptionPane.showMessageDialog(null, Localisation.getMessage("operationIsDone"), "", JOptionPane.INFORMATION_MESSAGE);
                StaticMethods.changeDefaultComboBoxModel(jcb, StaticMethods.getProbationerList().toArray());
            }
            ps.close();
            conn.close();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


}
